﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    using Model;
    using System.Data;
    using System.Data.SqlClient;
    public class DALStudent
    {
        public int Insert(Student stu)
        {
            string sql = @"insert into [S3].[dbo].[Student]
           ([Name],[Sex],[Birthday],[Height],[CID],[Readed])
     values
           (@Name,@Sex,@Birthday,@Height,@CID,@Readed)";
            SqlParameter[] sps = new SqlParameter[] {
                new SqlParameter("@Name",stu.Name),
                new SqlParameter("@Sex",stu.Sex),
                new SqlParameter("@Birthday",stu.Birthday),
                new SqlParameter("@Height",stu.Height),
                new SqlParameter("@CID",stu.CID),
                new SqlParameter("@Readed",stu.Readed),
            };
            return DBHelper.ExecuteNonQuery(sql, sps);
        }

        public Student SelectByID(string id)
        {
            string sql = "select * from [Student] where ID=@ID";
            Student stu = null;
            using (SqlDataReader sdr = DBHelper.ExecuteReader(sql, new SqlParameter("@ID", id)))
            {
                while (sdr.Read())
                {
                    stu = new Student()
                    {
                        Birthday = sdr["Birthday"] == DBNull.Value ?
                          (DateTime?)null : Convert.ToDateTime(sdr["Birthday"]),
                        CID = sdr["CID"] == DBNull.Value ?
                          (int?)null : Convert.ToInt32(sdr["CID"]),
                        Height = sdr["Height"] == DBNull.Value ?
                          (double?)null : Convert.ToDouble(sdr["Height"]),
                        ID = sdr["ID"] == DBNull.Value ?
                          (int?)null : Convert.ToInt32(sdr["ID"]),
                        Name = sdr["Name"] == DBNull.Value ?
                          null : Convert.ToString(sdr["Name"]),
                        Readed = sdr["Readed"] == DBNull.Value ?
                          (bool?)null : Convert.ToBoolean(sdr["Readed"]),
                        Sex = sdr["Sex"] == DBNull.Value ?
                          null : Convert.ToString(sdr["Sex"]),
                    };
                }
            }
            return stu;
        }

        public int Update(Student stu)
        {
            string sql = @"update [S3].[dbo].[Student]
   set [Name] = @Name,[Sex] = @Sex,[Birthday] = @Birthday
      ,[Height] = @Height,[CID] = @CID,[Readed] = @Readed
         where [ID]=@ID";
            SqlParameter[] sps = new SqlParameter[] {
                new SqlParameter("@Name",stu.Name),
                new SqlParameter("@Sex",stu.Sex),
                new SqlParameter("@Birthday",stu.Birthday),
                new SqlParameter("@Height",stu.Height),
                new SqlParameter("@CID",stu.CID),
                new SqlParameter("@Readed",stu.Readed),
                new SqlParameter("@ID",stu.ID),
            };

            return DBHelper.ExecuteNonQuery(sql,sps);
        }

        public int Delete(string id)
        {
            //string sql = "delete from [Student] where id =@id"; //参数化 处理in太难了
            //return DBHelper.ExecuteNonQuery(sql,new SqlParameter("@id",id));
            string sql = string.Format("delete from [Student] where id in ({0})",id);
            return DBHelper.ExecuteNonQuery(sql);

        }

     
    }
}
